from mysql_util import MysqlUtil
from  beautifultable import  BeautifulTable



def getstudent_info(student_number):
    sql = f"select {fields} from student where student_number={student_number}"
    result = db.find(sql, True)
    return result

def print_tabel( header_fields,result):
    tabel = BeautifulTable()
    tabel.columns.header = header_fields.split(',')
    if isinstance(result,list):
        for row in result:
            tabel.rows.append(row.values())
    else:
        tabel.rows.append(list(result.values()))
    print(tabel)


def confirm():
    getting = input("确认输入y/否则输入N:")
    if getting != 'y':
        return False
    else :
        return  True

def main():
    show_menu()

    while True:
        choice = input("请输入您的选择：")
        if choice == '1':#添加学生信息
            print("添加学生信息")
            student_number = input("请输入学生学号：")
            student_name = input("请输入学生姓名：")
            chinese = input("请输入语文成绩：")
            math = input("请输入数学成绩：")
            english = input("请输入英语成绩：")
            values = (student_number, student_name, chinese, math, english)
            sql = f"insert into student({fields}) values {values}"
            if not confirm():
                continue
            print(sql)
            result="添加成功" if db.execute(sql) else "添加失败"
            print(result)

        elif choice == '2':#查询学生成绩
            student_number = input("请输入学生学号：")
            result = getstudent_info(student_number)
            if not result:
                print("没有该学生信息")
                continue
            # tabel=BeautifulTable()
            # tabel.columns.header=fields.split(',')
            # tabel.rows.append(result.values())
            # print(tabel)
            print_tabel(fields,result)
        elif choice == '3':#显示所有学生成绩
            sql = f"select {fields} from student"
            result = db.find(sql, False)
            if not result:
                print("没有学生信息")
                continue

            # tabel = BeautifulTable()
            # tabel.columns.header = fields.split(',')
            # for row in result:
            #     tabel.rows.append(row.values())
            # print(tabel)
            print_tabel(fields,result)
        elif choice == '4':#删除学生成绩
            student_number =int(input("请输入学生学号："))
            if not confirm():
                continue
            sql=f"delete from student where student_number={student_number}"
            print(sql)
            result = "删除成功" if db.execute(sql) else "删除失败"
            print(result)
            # db.execute(sql)
        elif choice == '5':#修改学生成绩
            student_number = int(input("请输入学生学号："))
            updatecontent=input("请输入修改内容例如(chinese=90,math=90,english=90):")
            if not confirm():
                continue
            sql = f"update student set {updatecontent} where student_number={student_number}"
            print(sql)
            result = "修改成功" if db.execute(sql) else "修改失败"
            print(result)
        elif choice == '6':#总分成绩信息排名
            totalfields=",".join((fields,"chinese+math+english  as total"))
            orderby="total desc"
            sql = f"select {totalfields} from student order by {orderby}"
            print(sql)
            result = db.find(sql, False)
            if not result:
                print("没有学生信息")
                continue
            print(result)
            # tabel = BeautifulTable()
            # tabel.columns.header = totalfields.split(',')
            # for row in result:
            #     tabel.rows.append(row.values())
            # print(tabel)
            print_tabel(totalfields, result)
        elif choice == '7':#单科成绩排名
            subjecy = input("请输入查询科目，（例如chinese/math/english）")
            sql=f"select {fields},{subjecy} from student order by {subjecy} desc"
            print(sql)
            totalfields = ",".join((fields,"查询的科目："+subjecy))
            result = db.find(sql, False)
            if not result:
                print("没有查到科目信息")
                continue
            print(result)
            # tabel = BeautifulTable()
            # tabel.columns.header = totalfields.split(',')
            # for row in result:
            #     tabel.rows.append(row.values())
            # print(tabel)
            print_tabel(totalfields, result)
        elif choice == '8':#查询单科成绩（最高/最低/平均分）
            subjecy = input("请输入查询科目，（例如chinese/math/english）")
            sql = f"select  max({subjecy}) as highest,min({subjecy}) as lowest,AVG({subjecy}) as average from student"
            print(sql)
            result = db.find(sql, True)
            if not result:
                print("没有查到科目信息")
                continue
            count_fields = "highest, lowest, average"
            print(result)
            # tabel = BeautifulTable()
            # tabel.columns.header = count_fields.split(',')
            # tabel.rows.append(list(result.values()))
            # print(tabel)
            print_tabel(count_fields, result)
        elif choice == '0':
           print("欢迎下次再来")
           break
        else:
           print("输入错误，请重新输入")



def show_menu():
     print("---------------------------")
     print("                           ")

     print("1.添加学生成绩")
     print("2.查询学生成绩")
     print("3.显示所有学生成绩")
     print("4.删除学生成绩")
     print("5.修改学生成绩")
     print("6.总分成绩信息排名")
     print("7.单科成绩排名")
     print("8.查询单科成绩（最高/最低/平均分）")
     print("0.退出系统")


     print("                           ")
     print("---------------------------")



if __name__ == '__main__':
    db = MysqlUtil()
    fields = "student_number,student_name,chinese,math,english"
    main()
